package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.SystemUserHasRoleDao;
import csc.fresher.entity.SystemUserHasRole;

@Repository
public class SystemUserHasRoleDaoImpl implements SystemUserHasRoleDao {

	@Autowired
	DataSource dataSource;
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(
					"jdbc:mysql://20.203.182.23:3306/finalProject", "root", "root");
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	private static final Logger logger = Logger
			.getLogger(SystemUserHasRoleDaoImpl.class);

	@Override
	public long insertUserHasRole(SystemUserHasRole systemUserHasRole) {
		String query = "INSERT INTO `systemuser_has_role` (`SystemUserID`,`RoleID`) VALUES (?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			//connection = getConnection();
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setLong(1, systemUserHasRole.getSystemUserID());
			preparedStatement.setLong(2, systemUserHasRole.getRoleID());

			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				return rs.getLong(1);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return 0;
	}


	@Override
	public long deleteUserHasRole(long systemUserID) {
		String query = "delete from systemuser_has_role where SystemUserID = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			//connection = getConnection();
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setLong(1, systemUserID);
			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				return rs.getLong(1);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return 0;

	}

	@Override
	public List<SystemUserHasRole> getRoleBySystemUserID(long systemUserID) {
		String query = "SELECT * FROM `systemuser_has_role`,`systemuser` ";
		query += "WHERE systemuser.SystemUserID = systemuser_has_role.SystemUserID ";
		query += "AND systemuser_has_role.SystemUserID = " + systemUserID;
		List<SystemUserHasRole> systemUserHR = new ArrayList<SystemUserHasRole>();

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				systemUserHR.add(getSystemUserResultSet(rs));
				System.out.println(getSystemUserResultSet(rs).toString());
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return systemUserHR;
	}

	private SystemUserHasRole getSystemUserResultSet(ResultSet rs)
			throws SQLException {
		SystemUserHasRole systemUserHR = new SystemUserHasRole();
		systemUserHR.setSystemUserID(rs.getLong("SystemUserID"));
		systemUserHR.setRoleID(rs.getLong("RoleID"));

		return systemUserHR;
	}
	
	public static void main(String[] args) {
//		SystemUserHasRole sysUHR = new SystemUserHasRole(1,2);
//		SystemUserHasRoleDao sysUD = new SystemUserHasRoleDaoImpl();
//		System.out.println("insert done");
//		sysUD.delete(2);
//		sysUD.insert(sysUHR);
//		sysUD.getRoleBySystemUserID(2);
	 }
}
